package com.imist.demo1;

import com.imist.jdbcutil.JDBCUtil;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JDBCDemo5 {
    /**
     * 保存数据
     */
    @Test
    public void saveDemo(){
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        try {
            conn = JDBCUtil.getConnection();
            //先使用占位符标识变量
            String sql = "INSERT INTO user(username,password,name) VALUES (?,?,?)";
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,"zhangsan");
            preparedStatement.setString(2,"123456");
            preparedStatement.setString(3,"小武");
            //注意预处理语句执行不要加上sql参数
            int i = preparedStatement.executeUpdate();
            if (i > 0){
                System.out.println("保存成功");
            }else {
                System.out.println("保存失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.release(conn,preparedStatement);
        }
    }
    /**
     * 修改数据
     */
    @Test
    public void updateDemo(){
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        try {
            conn = JDBCUtil.getConnection();
            //先使用占位符标识变量
            String sql = "UPDATE user SET username = ? ,password=?,name=? WHERE uid = ?";
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,"zhangsanchange");
            preparedStatement.setString(2,"987654321");
            preparedStatement.setString(3,"小武test");
            preparedStatement.setInt(4,10);
            //注意预处理语句执行不要加上sql参数
            int i = preparedStatement.executeUpdate();
            if (i > 0){
                System.out.println("修改成功");
            }else {
                System.out.println("修改失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.release(conn,preparedStatement);
        }
    }
    /**
     * 删除数据
     */
    @Test
    public void deleteDemo(){
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        try {
            conn = JDBCUtil.getConnection();
            //先使用占位符标识变量
            String sql = "DELETE FROM user WHERE uid = ?";
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1,3);

            //注意预处理语句执行不要加上sql参数
            int i = preparedStatement.executeUpdate();
            if (i > 0){
                System.out.println("删除成功");
            }else {
                System.out.println("删除失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.release(conn,preparedStatement);
        }
    }
    /**
     * 查询数据
     */
    @Test
    public void selectDemo(){
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            conn = JDBCUtil.getConnection();
            //先使用占位符标识变量
            String sql = "SELECT * FROM user WHERE uid = ? ";
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1,4);

            //注意预处理语句执行不要加上sql参数
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                System.out.println(resultSet.getString("username") +"   " + resultSet.getString("password")+"    " +resultSet.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.release(conn,preparedStatement);
        }
    }
}
